﻿
/****** Object:  StoredProcedure [dbo].[Itinerary_getitinerary]    Script Date: 05/08/2008 09:52:40 ******/
CREATE PROC [dbo].[Itinerary_getitinerary]
	@name nvarchar(256),
	@major bigint = null,
	@minor bigint = null
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET ANSI_PADDING ON

DECLARE @nItinerarySetID as bigint

IF @major IS NOT NULL OR @minor IS NOT NULL
BEGIN 
	IF @major IS NULL
		SET @major = 0
	IF @minor IS NULL
		SET @minor = 0
		
	SELECT @nItinerarySetID=nItinerarySetID 
		FROM [dbo].[Itinerary]
		WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name) AND nMajor = @major AND nMinor = @minor AND nStatus = 1
	IF @@ROWCOUNT = 0
		RETURN -1

	--IF dbo.re_artifact_access_allowed(@nRuleSetID, 1, 1)=0
		--RETURN -2
END
ELSE
BEGIN
	SELECT	@nItinerarySetID=nItinerarySetID,
			@major = nMajor,
			@minor = nMinor		 
		FROM [dbo].[Itinerary]
		WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name) AND nStatus = 1
		AND (nMajor * 10 + nMinor) = (SELECT MAX(nMajor * 10 + nMinor) FROM [dbo].[Itinerary] WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name)) 
	
	IF @@ROWCOUNT = 0
		RETURN -1
END

SELECT nMajor, nMinor, imITML 
		FROM [dbo].[Itinerary]
		WHERE strName = @name AND DATALENGTH(strName) = DATALENGTH(@name) AND nMajor = @major AND nMinor = @minor AND nStatus = 1
	RETURN 0

GO
--GRANT EXECUTE
--    ON OBJECT::[dbo].[Itinerary_getitinerary] TO [ESBUsers]
--    AS [dbo];

